package com.mycompany.inventory.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;

import com.mycompany.common.AbstractDao;
import com.mycompany.common.jdbc.ConnectionDatabase;
import com.mycompany.inventory.pojo.Address;
import com.mycompany.inventory.pojo.AddressOwner;
import com.mycompany.inventory.pojo.CustomInfo;
import com.mycompany.inventory.pojo.Vendor;

/**
 * Created by My Company.
 * User: loitran
 * Date: Feb 29, 2012
 * Time: 1:53:19 PM
 * com.mycompany.inventory.persistence.
 */
public class VendorDAO extends AbstractDao<Vendor>{
	
	private Connection con = this.connect;
	public static final String SELECT_ALL_SQL = "select * from oce_vendor order by createdStamp DESC";	
	public static final String SELECT_PRODUCT_VENDOR_SQL = "SELECT a.* FROM oce_vendor a, oce_product_vendor b, oce_product c WHERE c.product_id = b.product_id AND a.vendor_id = b.vendor_id AND b.product_id = ?";
	public static final String SELECT_SQL = "select * from oce_vendor where vendor_id = ?";
	public static final String DELETE_SQL = "DELETE FROM oce_vendor WHERE vendor_id = ?";
	public static final String INSERT_SQL = "insert into oce_vendor (vendor_id,vendor_name,remark,contact_id,paymentTermId," +
			   "taxSchemeId,currencyId,carrierId,customvalue1,customvalue2,customvalue3,customvalue4,customvalue5,customvalue6," +
			   "customvalue7,customvalue8,customvalue9,customvalue10) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	
	public static final String UPDATE_SQL = "UPDATE oce_vendor SET vendor_name = ?, remark = ?, paymentTermId = ?, taxSchemeId = ? , " +
		                                	"currencyId = ? , carrierId = ?, customvalue1 = ?, customvalue2 = ? , customvalue3 = ?, customvalue4 = ?, customvalue5 = ?, contact_id = ? " +
		                                	"WHERE vendor_id = ? ";
	public static final String SELECT_LAST_VENDOR_SQL = "select v.* FROM oce_vendor v, oce_product p WHERE p.lastVendor = v.vendor_id AND p.product_id = ?";
	
	public Vendor create(Vendor obj){
		try{
			
			con.setAutoCommit(false);
			
			if(obj == null || obj.getId() == null || obj.getId().equals(""))
				return obj;
			
			if(!obj.getContact().getName().equals("") || !obj.getContact().getPhone().equals("") || !obj.getContact().getFax().equals("") 
					|| !obj.getContact().getEmail().equals("") || !obj.getContact().getWebsite().equals("")){
				ContactDAO dao = new ContactDAO();
				dao.create(obj.getContact());
			}
						
			
			PreparedStatement ps = con.prepareStatement(INSERT_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			
			long id = 0l;
			ps.setLong(1,id);
			ps.setString(2, obj.getName());
			ps.setString(3, obj.getRemark());
			if(obj.getContact().getId().equals("")){
				ps.setString(4, null);
			}else{
				ps.setLong(4,Long.parseLong(obj.getContact().getId()));
			}
			
			if(obj.getPaymentTerm()== null || obj.getPaymentTerm().getId().equals("")){
				ps.setString(5, null);
			}else{
				ps.setLong(5,Long.parseLong(obj.getPaymentTerm().getId()));
			}
			
			if(obj.getTaxingScheme()== null || obj.getTaxingScheme().getId().equals("")){
				ps.setString(6, null);
			}else{
				ps.setLong(6,Long.parseLong(obj.getTaxingScheme().getId()));
			}
			
			if(obj.getCurrency()== null || obj.getCurrency().getId().equals("")){
				ps.setString(7, null);
			}else{
				ps.setLong(7,Long.parseLong(obj.getCurrency().getId()));
			}
			
			if(obj.getCarrier()== null || obj.getCarrier().getId().equals("")){
				ps.setString(8, null);
			}else{
				ps.setLong(8,Long.parseLong(obj.getCarrier().getId()));
			}
		    
			int idx = 0;
			int databaseIdx = 9;
			for(CustomInfo cus : obj.getCustoms()){
				if(cus.isShowed()){
					ps.setString(databaseIdx,  obj.getCustoms().get(idx).getValue());
					idx++;
					databaseIdx++;
				}
			}
			
			for(int i = databaseIdx ; i< 19 ; i++){
				ps.setString(i, "");
			}
			
			ConnectionDatabase.executeUpdate(ps);
			ResultSet rs=ps.getGeneratedKeys();
            if(rs.next()){
                Integer Id = (Integer) rs.getInt(1);               
                obj.setId(String.valueOf(Id));
            }
            
            //update address
            AddressDAO addDao = new AddressDAO();
			AddressOwnerDAO addOwnDao = new AddressOwnerDAO();
			
			for(Address add : obj.getAddresses()){
				if(!add.getAddress().equals("")){
					addDao.create(add);
					addOwnDao.create(new AddressOwner("1", obj.getId(), add.getId(),"Vendor" , add.getType()));
				}
			}
			
			//update custom
			CustomFieldDAO cusDao = new CustomFieldDAO();
			idx = 1;
			for(CustomInfo cus : obj.getCustoms()){
				if(cus.isShowed()){
					cus.setId(String.valueOf(idx));
					cusDao.update(cus);
					idx++;					
				}
			}
			
			for(int i = idx ; i< 6 ; i++){				
				cusDao.update(new CustomInfo("", "", String.valueOf(i), "Vendor", false));
			}
			
			con.commit();
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			try{
				con.rollback();
			}catch (SQLException e1){}
			obj = null;
		}finally {
			try{
				con.setAutoCommit(true);
			}catch (SQLException e1){}
		}
		return obj;
	}
	
	public Vendor update(Vendor obj){
		try{
			con.setAutoCommit(false);
			
			ContactDAO dao = new ContactDAO();
			if(obj.getContact()!= null && obj.getContact().getId()!= null && !obj.getContact().getId().equals("")){				
				dao.update(obj.getContact());
			}else{
				if(!obj.getContact().getName().equals("") || !obj.getContact().getPhone().equals("") || !obj.getContact().getFax().equals("") 
						|| !obj.getContact().getEmail().equals("") || !obj.getContact().getWebsite().equals("")){
					dao.create(obj.getContact());
				}
			}
				
			
			PreparedStatement ps = con.prepareStatement(UPDATE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
						
			ps.setString(1, obj.getName());
			ps.setString(2, obj.getRemark());			
			
			if(obj.getPaymentTerm()== null || obj.getPaymentTerm().getId().equals("")){
				ps.setString(3, null);
			}else{
				ps.setLong(3,Long.parseLong(obj.getPaymentTerm().getId()));
			}
			
			if(obj.getTaxingScheme()== null || obj.getTaxingScheme().getId().equals("")){
				ps.setString(4, null);
			}else{
				ps.setLong(4,Long.parseLong(obj.getTaxingScheme().getId()));
			}
			
			if(obj.getCurrency()== null || obj.getCurrency().getId().equals("")){
				ps.setString(5, null);
			}else{
				ps.setLong(5,Long.parseLong(obj.getCurrency().getId()));
			}
			
			if(obj.getCarrier()== null || obj.getCarrier().getId().equals("")){
				ps.setString(6, null);
			}else{
				ps.setLong(6,Long.parseLong(obj.getCarrier().getId()));
			}
		
			int idx = 0;
			int databaseIdx = 7;
			for(CustomInfo cus : obj.getCustoms()){
				if(cus.isShowed()){
					ps.setString(databaseIdx,  obj.getCustoms().get(idx).getValue());
					idx++;
					databaseIdx++;
				}
			}
			
			for(int i = databaseIdx ; i< 12 ; i++){
				ps.setString(i, "");
			}
			
			ps.setLong(13, Long.parseLong(obj.getId()));
			if(obj.getContact()== null || obj.getContact().getId() == null || obj.getContact().getId().equals("")){
				ps.setString(12, null);
			}else{
				ps.setLong(12, Long.parseLong(obj.getContact().getId()));
			}
			
									
			ConnectionDatabase.executeUpdate(ps);		
			
			
			AddressDAO addDao = new AddressDAO();
			AddressOwnerDAO addOwnDao = new AddressOwnerDAO();
			
			for(Address add : obj.getAddresses()){
				if(!add.getId().equals("")){
					addDao.update(add);
					//addOwnDao.update(new AddressOwner("1", obj.getId(), add.getId(),"Vendor" , add.getType()));
				}else{
					if(add.getId().equals("") && !add.getAddress().equals("")){
						addDao.create(add);
						addOwnDao.create(new AddressOwner("1", obj.getId(), add.getId(),"Vendor" , add.getType()));
					}
				}
			}
			
			//update custom
			CustomFieldDAO cusDao = new CustomFieldDAO();
			idx = 1;
			for(CustomInfo cus : obj.getCustoms()){
				if(cus.isShowed()){
					cus.setId(String.valueOf(idx));
					cusDao.update(cus);
					idx++;					
				}
			}
			
			for(int i = idx ; i< 6 ; i++){				
				cusDao.update(new CustomInfo("", "", String.valueOf(i), "Vendor", false));
			}
			
			con.commit();
			ps.close();
		}catch (Exception e) {
			try{
				con.rollback();
			}catch (SQLException e1){}
			obj = null;
		}finally {
			try{
				con.setAutoCommit(true);
			}catch (SQLException e1){}
		}
		return obj;
	}
	
	public Vendor find(long id){
		List<Vendor> vendors = new ArrayList<Vendor>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			vendors = convert(ConnectionDatabase.executeQuery(ps));			
		}catch (Exception e) {
			// TODO: handle exception
		}
		return vendors.size() == 0 ? null : vendors.get(0);
	}

	public List<Vendor> findLastVendor(long id){
		List<Vendor> vendors = new ArrayList<Vendor>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_LAST_VENDOR_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			vendors = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
			vendors = null;
		}
		return vendors;
	}	
	
	public List<Vendor> findAll(){
		List<Vendor> vendors = new ArrayList<Vendor>();
		try{
			vendors = convert(ConnectionDatabase.executeQuery(con.prepareStatement(SELECT_ALL_SQL, PreparedStatement.RETURN_GENERATED_KEYS)));
		}catch (Exception e) {
			// TODO: handle exception
			vendors = null;
		}
		return vendors;
	}
	
	public List<Vendor> findByProductID(long id){
		List<Vendor> vendors = new ArrayList<Vendor>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_PRODUCT_VENDOR_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			vendors = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
			vendors = null;
		}
		return vendors;
	}
	
	public void delete(Vendor obj){
		try{
			con.setAutoCommit(false);
			
			if(obj.getContact() != null && !obj.getContact().getId().equals("")){					
				ContactDAO dao = new ContactDAO();
				dao.delete(obj.getContact());
			}
			
			AddressDAO addDao = new AddressDAO();
			AddressOwnerDAO addOwnDao = new AddressOwnerDAO();
			
			for(Address add : obj.getAddresses()){
				if(!add.getId().equals("")){
					addDao.delete(add);					
				}
			}
			
			addOwnDao.delete(new AddressOwner("1", obj.getId(), "" ,"Vendor" , ""));
			
			PreparedStatement ps = con.prepareStatement(DELETE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,Long.parseLong(obj.getId()));
			
			ConnectionDatabase.executeUpdate(ps);	
			
			con.commit();
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			try{
				con.rollback();
			}catch (SQLException e1){}
			obj = null;
		}finally {
			try{
				con.setAutoCommit(true);
			}catch (SQLException e1){}
		}
	}
			
	private List<Vendor> convert(ResultSet rs) throws SQLException {
		List<Vendor> vendors = new ArrayList<Vendor>();
		ContactDAO conDao = new ContactDAO();
		PaymentTermDAO paymentDao = new PaymentTermDAO();
		TaxSchemeDAO taxDao = new TaxSchemeDAO();
		CarrierDAO carrierDao = new CarrierDAO();
		CurrencyDAO curDao = new CurrencyDAO();
		AddressDAO addDao = new AddressDAO();
		CustomFieldDAO cusDao = new CustomFieldDAO();
		
		while (rs.next()) {
	        // Get the data from the row using the column index
	        String id = rs.getString(1);
	        String name = rs.getString(2);
	        String remark = rs.getString(3);
	        String contactId = rs.getString(4);
	        String paymentTermId = rs.getString(5);
	        String taxSchemeId = rs.getString(6);
	        String currencyId = rs.getString(7);
	        String carrierId = rs.getString(8);
	        String customvalue1 = rs.getString(9);
	        String customvalue2 = rs.getString(10);
	        String customvalue3 = rs.getString(11);
	        String customvalue4 = rs.getString(12);
	        String customvalue5 = rs.getString(13);	  		         
	        
	        List<CustomInfo> cus = new ArrayList();
			cus.add(new CustomInfo("Alternate Contact", customvalue1,"","",true));
			cus.add(new CustomInfo("Alternate Contact2", customvalue2,"","",true));
			cus.add(new CustomInfo("Alternate Contact3", customvalue3,"","",true));
			cus.add(new CustomInfo("Alternate Contact4", customvalue4,"","",true));
			cus.add(new CustomInfo("Emergency Phone", customvalue5,"","",true));
			
			List<Address> adds = new ArrayList();
			adds = addDao.findByVendorID(Long.parseLong(id));
	        
	        vendors.add(new Vendor(id, name, 0, adds , contactId == null ? null : conDao.find(Long.parseLong(contactId)), 
	        		    cus, remark,
	        		    paymentTermId == null ? null : paymentDao.find(Long.parseLong(paymentTermId)),
	        		    taxSchemeId == null ? null : taxDao.find(Long.parseLong(taxSchemeId)),
	        		    currencyId == null ? null : carrierDao.find(Long.parseLong(currencyId)),
	        		    carrierId == null ? null : curDao.find(Long.parseLong(carrierId))));
	    }
		rs.close();
		return vendors;
	}	
}
